﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Common;
using MySql.Data.MySqlClient;
using System.Data;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Configuration;
using System.Text;
using System.Data.SqlClient;

/// <summary>
/// An abstract database connection which can connect to different server types
/// </summary>
public class Database
{
    public enum DatabaseType {
        [EnumBinder.EnumDescription("MySQL")]
        MySQL,

        [EnumBinder.EnumDescription("MS SQL Server")]
        SQLServer
    }

    private DbConnection conn;
    private DatabaseType type;
    private String tablePrefix;

    /// <summary>
    /// Gets or sets the type of this database instance.
    /// </summary>
    /// <value>The type of the instance.</value>
    public DatabaseType InstanceType {
        get { return type; }
        set { type = value; }
    }

    /// <summary>
    /// Gets a value indicating whether the connection is open.
    /// </summary>
    /// <value><c>true</c> if connected, otherwise <c>false</c>.</value>
    public bool Connected {
        get {
            if(conn != null && conn.State == ConnectionState.Open)
                return true;
            else
                return false;
        }
    }

    public string TablePrefix {
        get { return tablePrefix; }
    }

    /// <summary>
    /// Initializes a new <see cref="Database"/>.
    /// </summary>
	public Database() {
        ConnectionStringSettings config = WebConfigurationManager.ConnectionStrings["Default"];
        tablePrefix = WebConfigurationManager.AppSettings["tablePrefix"];
        switch(config.ProviderName) {
            case "MySql.Data.MySqlClient":
                this.type = DatabaseType.MySQL;
                conn = new MySqlConnection(config.ConnectionString);
                break;
            case "System.Data.SqlClient":
                this.type = DatabaseType.SQLServer;
                conn = new SqlConnection(config.ConnectionString);
                break;
        }
	}

    /// <summary>
    /// Opens a connection.
    /// </summary>
    /// <param name="type">The server type.</param>
    /// <param name="connectionString">The connection string.</param>
    public Database(DatabaseType type, string connectionString, string tablePrefix) {
        this.type = type;
        this.tablePrefix = tablePrefix;
        switch(type) {
            case DatabaseType.MySQL:
                conn = new MySqlConnection(connectionString);
                break;
            case DatabaseType.SQLServer:
                conn = new SqlConnection(connectionString);
                break;
        }
        conn.Open();
    }

    /// <summary>
    /// Opens a connection using the saved connection string (ConnectionStrings["Default"]).
    /// </summary>
    public void OpenConnection() {
        if(conn.State != ConnectionState.Open) {
            conn.Open();
        }
    }

    /// <summary>
    /// Opens a connection.
    /// </summary>
    /// <param name="type">The server type.</param>
    /// <param name="connectionString">The connection string.</param>
    public void OpenConnection(DatabaseType type, string connectionString) {
        this.type = type;
        switch(type) {
            case DatabaseType.MySQL:
                conn = new MySqlConnection(connectionString);
                break;
            case DatabaseType.SQLServer:
                conn = new SqlConnection(connectionString);
                break;
        }
        conn.Open();
    }

    /// <summary>
    /// Closes the connection.
    /// </summary>
    public void CloseConnection() {
        conn.Close();
    }

    /// <summary>
    /// Creates a database command.
    /// </summary>
    /// <returns></returns>
    public Command CreateCommand() {
        return new Command(type, conn.CreateCommand());
    }

    /// <summary>
    /// Begins a transaction.
    /// </summary>
    /// <returns></returns>
    public DbTransaction BeginTransaction() {
        return conn.BeginTransaction();
    }

    /// <summary>
    /// Creates the tables needed by ResourceBlender.
    /// </summary>
    /// <param name="applications"><c>true</c> if the applications table should be created.</param>
    /// <param name="elements"><c>true</c> if the elements table should be created.</param>
    /// <param name="resources"><c>true</c> if the resources table should be created.</param>
    /// <param name="resourceComments"><c>true</c> if the resource comments table should be created.</param>
    /// <param name="users"><c>true</c> if the users table should be created.</param>
    /// <param name="templates"><c>true</c> if the templates table should be created.</param>
    public void CreateTables(bool applications, bool elements, bool resources, bool resourceComments, bool users, bool templates) {
        if(conn == null) {
            throw new InvalidOperationException("Connection not initialized");
        } else {
            if(conn.State != ConnectionState.Open) {
                OpenConnection();
            }
            using(Command cmd = CreateCommand()) {
                StringBuilder sqlBuffer = new StringBuilder();
                switch(type) {
                    case DatabaseType.MySQL:
                        if(applications) {
                            sqlBuffer.Append(@"CREATE TABLE `").Append(TablePrefix).Append(@"applications` (
                                          `id` varchar(10) NOT NULL default '',
                                          `displayname` varchar(255) NOT NULL,
                                          `name` varchar(255) NOT NULL,
                                          `defaultlanguage` varchar(7) NOT NULL,
                                          `languages` text NOT NULL,
                                          PRIMARY KEY  (`id`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                            sqlBuffer.Append(@"CREATE TABLE `").Append(TablePrefix).Append(@"bundles` (
                                          `id` varchar(10) NOT NULL,
                                          `name` varchar(255) NOT NULL,
                                          `application` varchar(10) NOT NULL,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `unique_bundle` (`name`,`application`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(elements) {
                            sqlBuffer.Append(@"CREATE TABLE `").Append(TablePrefix).Append(@"elements` (
                                          `id` int(11) NOT NULL auto_increment,
                                          `applicationid` varchar(10) NOT NULL,
                                          `bundle` varchar(10) NOT NULL,
                                          `elementname` varchar(255) NOT NULL,
                                          `resourceid` int(10) default NULL,
                                          `comment` text,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `uniqueelement` (`applicationid`,`elementname`,`bundle`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(resources) {
                            sqlBuffer.Append(@"CREATE TABLE `").Append(TablePrefix).Append(@"resources` (
                                          `resourceid` int(10) NOT NULL default '0',
                                          `language` varchar(10) NOT NULL,
                                          `translation` text NOT NULL,
                                          `translatorcomment` text,
                                          PRIMARY KEY  (`resourceid`,`language`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(resourceComments) {
                            sqlBuffer.Append(@"CREATE TABLE `").Append(TablePrefix).Append(@"resourcecomments` (
                                          `resourceid` int(10) NOT NULL,
                                          `comment` text,
                                          PRIMARY KEY  (`resourceid`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(users) {
                            sqlBuffer.Append(@"CREATE TABLE `").Append(TablePrefix).Append(@"users` (
                                          `id` int(255) NOT NULL auto_increment,
                                          `username` varchar(255) NOT NULL,
                                          `password` varchar(255) NOT NULL,
                                          `email` text,
                                          `permissions` int(11) NOT NULL default '0',
                                          `allowedprojects` text,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `unique_username` (`username`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(templates) {
                            sqlBuffer.Append(@"CREATE TABLE `").Append(TablePrefix).Append(@"templates` (
                                              `id` int(5) NOT NULL auto_increment,
                                              `name` varchar(255) NOT NULL,
                                              `filename` varchar(255) NOT NULL,
                                              `filestart` text NOT NULL,
                                              `line` varchar(255) NOT NULL,
                                              `fileend` text NOT NULL,
                                              PRIMARY KEY  (`id`),
                                              UNIQUE KEY `unique_name` (`name`)
                                            ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                            sqlBuffer.Append(@"INSERT INTO `").Append(TablePrefix).Append(@"templates` (`name`, `filename`, `filestart`, `line`, `fileend`) VALUES 
                                             ('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(""{=Element}"",""{=Translation}"");', '?>'),
                                             ('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php\r\n${=Bundle} = array(', '""{=Element}"" => ""{=Translation}"",', '); ?>');");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }
                        break;
                    case DatabaseType.SQLServer:
                        if(applications) {
                            sqlBuffer.Append(@"CREATE TABLE [dbo].[").Append(TablePrefix).Append(@"applications](
	                                        [id] [varchar](10) NOT NULL,
	                                        [displayname] [nvarchar](max) NOT NULL,
	                                        [name] [nvarchar](max) NOT NULL,
	                                        [defaultlanguage] [varchar](7) NOT NULL,
	                                        [languages] [varchar](max) NOT NULL
                                        CONSTRAINT [PK_").Append(TablePrefix).Append(@"applications] PRIMARY KEY CLUSTERED ([id] ASC))");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                            sqlBuffer.Append(@"CREATE TABLE [dbo].[").Append(TablePrefix).Append(@"bundles] (
                                            [id] [varchar](10) NOT NULL,
	                                        [name] [nvarchar](200) NOT NULL,
                                            [application] [varchar](10) NOT NULL,
                                        CONSTRAINT [PK_").Append(TablePrefix).Append(@"bundle] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [unique_").Append(TablePrefix).Append(@"bundle] UNIQUE NONCLUSTERED ([name] ASC,[application] ASC))");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(elements) {
                            sqlBuffer.Append(@"CREATE TABLE [dbo].[").Append(TablePrefix).Append(@"elements](
	                                        [id] [int] IDENTITY(1,1) NOT NULL,
	                                        [applicationid] [varchar](10) NOT NULL,
	                                        [bundle] [varchar](10) NOT NULL,
	                                        [elementname] [nvarchar](100) NOT NULL,
	                                        [resourceid] [int] NULL,
	                                        [comment] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_").Append(TablePrefix).Append(@"element] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_").Append(TablePrefix).Append(@"uniqueelement] UNIQUE NONCLUSTERED ([applicationid] ASC,[bundle] ASC,[elementname] ASC))");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(resources) {
                            sqlBuffer.Append(@"CREATE TABLE [dbo].[").Append(TablePrefix).Append(@"resources](
	                                        [resourceid] [int] NOT NULL,
	                                        [language] [varchar](10) NOT NULL,
	                                        [translation] [nvarchar](max) NOT NULL,
                                            [translatorcomment] nvarchar(max) NULL,
                                        CONSTRAINT [PK_").Append(TablePrefix).Append(@"resources] PRIMARY KEY CLUSTERED ([resourceid] ASC, [language] ASC))");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(resourceComments) {
                            sqlBuffer.Append(@"CREATE TABLE [dbo].[").Append(TablePrefix).Append(@"resourcecomments](
	                                        [resourceid] [int] NOT NULL,
	                                        [comment] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_").Append(TablePrefix).Append(@"resourcecomments] PRIMARY KEY CLUSTERED ([resourceid] ASC))");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(users) {
                            sqlBuffer.Append(@"CREATE TABLE [dbo].[").Append(TablePrefix).Append(@"users](
	                                        [id] [int] IDENTITY(1,1) NOT NULL,
	                                        [username] [nvarchar](100) NOT NULL,
	                                        [password] [nvarchar](100) NOT NULL,
	                                        [email] [nvarchar](100) NULL,
	                                        [permissions] [int] NOT NULL,
	                                        [allowedprojects] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_").Append(TablePrefix).Append(@"users] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_").Append(TablePrefix).Append(@"uniqueusername] UNIQUE NONCLUSTERED ([username] ASC))");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(templates) {
                            sqlBuffer.Append(@"CREATE TABLE [dbo].[").Append(TablePrefix).Append(@"templates] (
                                              [id] [int] IDENTITY(10,1) NOT NULL,
                                              [name] [nvarchar](100) NOT NULL,
                                              [filename] [nvarchar](max) NOT NULL,
                                              [filestart] [nvarchar](max) NOT NULL,
                                              [line] [nvarchar](max) NOT NULL,
                                              [fileend] [nvarchar](max) NOT NULL,
                                        CONSTRAINT [PK_").Append(TablePrefix).Append(@"templates] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_").Append(TablePrefix).Append(@"templatename] UNIQUE NONCLUSTERED ([name] ASC))");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                            sqlBuffer.Append(@"INSERT INTO [dbo].[").Append(TablePrefix).Append(@"templates] ([name], [filename], [filestart], [line], [fileend]) VALUES 
                                             ('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(""{=Element}"",""{=Translation}"");', '?>'),
                                             ('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php' + CHAR(13)+CHAR(10) + '${=Bundle} = array(', '""{=Element}"" => ""{=Translation}"",', '); ?>');");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0,sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }
                        break;
                }
            }
        }
    }

    /// <summary>
    /// Drops all tables created by ResourceBlender.
    /// </summary>
    /// <param name="applications"><c>true</c> if the applications table should be dropped.</param>
    /// <param name="elements"><c>true</c> if the elements table should be dropped.</param>
    /// <param name="resources"><c>true</c> if the resources table should be dropped.</param>
    /// <param name="resourceComments"><c>true</c> if the resource comments table should be dropped.</param>
    /// <param name="users"><c>true</c> if the users table should be dropped.</param>
    public void DropTables(bool applications, bool elements, bool resources, bool resourceComments, bool users, bool templates) {
        if(conn == null) {
            throw new InvalidOperationException("Connection not initialized");
        } else {
            if(conn.State != ConnectionState.Open) {
                OpenConnection();
            }
            using(Command cmd = CreateCommand()) {
                if(applications) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "applications;";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "bundles;";
                    cmd.ExecuteNonQuery();
                }
                if(elements) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "elements";
                    cmd.ExecuteNonQuery();
                }
                if(resources) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "resources";
                    cmd.ExecuteNonQuery();
                }
                if(resourceComments) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "resourcecomments";
                    cmd.ExecuteNonQuery();
                }
                if(templates) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "templates";
                    cmd.ExecuteNonQuery();
                }
                if(users) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "users";
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }

    /// <summary>
    /// Generates an IN clause or an empty string if there are no values.
    /// </summary>
    /// <param name="fieldName">Name of the field.</param>
    /// <param name="values">The values to include.</param>
    /// <param name="useAnd">If <c>true</c>, the clause will be prepended by AND.</param>
    /// <returns>An SQL IN clause</returns>
    public string GenerateInClause(string fieldName, string[] values, bool useAnd) {
        if(values.Length == 0) {
            return string.Empty;
        } else {
            StringBuilder buffer = new StringBuilder(useAnd ? " AND " : " ");
            buffer.Append(fieldName).Append(" IN('").Append(Utils.Implode(values, "', '", false)).Append("')");
            return buffer.ToString();
        }
    }

    /// <summary>
    /// Creates a connection string.
    /// </summary>
    /// <param name="type">The server type.</param>
    /// <param name="host">The host.</param>
    /// <param name="port">The port.</param>
    /// <param name="user">The username.</param>
    /// <param name="pass">The password.</param>
    /// <param name="database">The database name.</param>
    /// <returns></returns>
    public static string GetConnectionString(DatabaseType type, string host, int port, string user, string pass, string database) {
        switch(type) {
            case DatabaseType.MySQL:
                return string.Format(Constants.MySQLConnecionString, host, port, database, user, pass);
            case DatabaseType.SQLServer:
                return string.Format(Constants.SQL2008ConnectionString, host, port, database, user, pass);
        }
        return string.Empty;
    }

    public class DataAdapter {
        private Command cmd;
        private DbDataAdapter adapter;

        public DataAdapter(Command cmd) {
            this.cmd = cmd;
            switch(cmd.DBType) {
                case DatabaseType.MySQL:
                    this.adapter = new MySqlDataAdapter(cmd.InnerCommand as MySqlCommand);
                    break;
                case DatabaseType.SQLServer:
                    this.adapter = new SqlDataAdapter(cmd.InnerCommand as SqlCommand);
                    break;
            }
        }

        public void Fill(System.Data.DataSet ds) {
            adapter.Fill(ds);
        }

    }

    public class Command : IDisposable {
        public DbCommand InnerCommand { get; set; }
        public DatabaseType DBType { get; set; }

        public DbParameterCollection Parameters {
            get { return InnerCommand.Parameters; }
        }

        public string CommandText {
            get { return InnerCommand.CommandText; }
            set { InnerCommand.CommandText = value; }
        }

        public Command(DatabaseType type, DbCommand cmd) {
            this.DBType = type;
            this.InnerCommand = cmd;
        }

        public void AddParameterWithValue(string name, object value) {
            switch(DBType) {
                case DatabaseType.MySQL:
                    (InnerCommand as MySqlCommand).Parameters.AddWithValue(name, value);
                    break;
                case DatabaseType.SQLServer:
                    (InnerCommand as SqlCommand).Parameters.AddWithValue(name, value);
                    break;
            }
        }

        public DbDataReader ExecuteReader() {
            return InnerCommand.ExecuteReader();
        }
        public DbDataReader ExecuteReader(CommandBehavior behaviour) {
            return InnerCommand.ExecuteReader(behaviour);
        }
        public object ExecuteScalar() {
            return InnerCommand.ExecuteScalar();
        }

        public int ExecuteNonQuery() {
           return InnerCommand.ExecuteNonQuery();
        }

        #region IDisposable Members
        public void Dispose() {
            InnerCommand.Dispose();
        }
        #endregion
    }
}
